今天的主題來透過應用實例複習常用SQL語法，普通的解釋相對無聊～所以我們邊舉例邊看過程中可能遇到的問題。

• 情境模擬

需求: 設計一個投資遊戲活動管控介面。

內容: 活動的(新增/刪除/查詢/更新)。 ( 這邊不會提到程式API設計！！會偏題

• 資料表設計

接到需求時，想一下可能需要用到什麼欄位？

Example. 針對此需求會用到以下欄位-

活動ID , 活動名稱 , 活動語言 , 更新時間 , 建立時間 , 活動開始時間 , 活動結束時間 , 遊戲名稱 , 遊戲玩法 , 活動狀態 , 平台ID ...。

• 新增資料庫
  • active

CREATE DATABASE active;

• 新增資料表:
  • act(活動狀態)
  • act_game(遊戲項目內容)
  • act_name(活動內容)

(補充)

這邊補充一下新手建表時欄位型別上，一開始會遇到的問題～

欄位宣告時 INT(11) / DECIMAL(3,2) / VARCHAR(10) 後面帶的數字會影響長度還是？

1. VARCHAR(10) => 欄位值可以儲存10個字符。
2. DECIMAL(3,2) => 欄位總共顯示3位數(含小數點後2位)。ex.DECIMAL(3,2)範圍: -9.99~9.99
3. 這邊以INT型態解釋差異=> INT(11) vs. INT(20) vs. INT(20) UNSIGNED ZEROFILL

類型| 字節 |最小值(Signed)| 最大值(Signed)| 最小值(Unsigned)| 最大值(Unsigned)
------------- | -------------
INT |4 |-2147483648|2147483647| 0 |4294967295

1. 欄位名稱 int(11)
2. 欄位名稱 int(11) UNSIGNED ZEROFILL。
   • UNSIGNED -> 避免存入負的數值(對於浮點數型態而言只是避免負值)，而且對整數型態來說還可以儲存更大的正整數值。ex. INT(11) UNSIGNED (0~4294967295)
   • ZEROFILL -> 表示如果存儲的數字少於N個字符，將在數字左側補零。
     

重點整理:

• 像我們一般時的宣告ex. INT(11), INT(20) 不會影響使用上的值範圍！！
  • INT(11) & INT(20) 所代表的欄位儲存最(大/小)範圍值皆為(-2147483648 ~ 2147483647)。
• 真的會影響是當你加上UNSIGNED ZEROFILL時才有作用！ 不僅範圍值會改變，還會有補0作用。

回到建立資料表部分，我們知道資料表上大概要有哪些欄位了那重點來了primary key, index 如何設定 ?

在設計資料表的過程中索引的設計會跟查詢效率有直接的關係，隨著數據量的增加一條查詢SQL語句有無吃到index對執行上的效率差異是很明顯的～

注意: Primary key 在整張表的紀錄中一定是唯一的，不可以重複!!

• 新增資料表
  • act (紀錄活動狀態)

我們知道clustered index決定我們數據庫中的數據分佈，數據會依照clustered index key值排序，所以這邊選擇自增欄位的唯一性當做我們的主索引鍵(PK)。 ps. 不懂為何選擇自增欄位更好!? 請參考前2天文章

CREATE TABLE `act` (
 `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活動ID',
 `status` tinyint(4) NOT NULL COMMENT '活動狀態',
 `start_time` int(11) unsigned NOT NULL COMMENT '活動開始時間',
 `end_time` int(11) unsigned NOT NULL COMMENT '活動結束時間',
 `platform_id` bigint(20) NOT NULL COMMENT '平台ID',
 `updated_at` int(11) unsigned NOT NULL COMMENT '更新時間',
 `created_at` int(11) unsigned NOT NULL COMMENT '建立時間',
 PRIMARY KEY (`actives_id`)
) 

(補充)

• 複合索引

一般來說我們使用一個欄位作為普通索引，而兩個欄位(含)以上所組成的索引稱為複合索引。

• 複合索引採最左匹配原則

1. 欄位順序會關係到索引是否能用上，在查詢條件中索引最左邊的列被匹配到，後面的列才可能繼續匹配。

2. 假設針對a,b,c欄位建立了複合索引 index(a,b,c) 相當於有index(a)和(a,b)和(a,b,c)會吃到該組索引。

3. 組合上使用頻率高最具識別性的欄位放置最左，提升索引使用率。

Example. 針對act表建立一個平台&狀態的複合索引～

mysql> ALTER TABLE act ADD INDEX platform_id_status(platform_id,status);

使用查詢語法分析可以看到:

第一句SQL 和 第二句SQL: 符合最左匹配原則，皆吃得到該index。

第三句SQL: 因為條件只有status，根據最左匹配原則platform_id沒被匹配到，所以吃不到該組index。

• 新增資料表
  • act_game (遊戲項目內容)
  • act_name (活動內容)

在act_game這張資料表主要查詢投資活動的遊戲玩法支援，一個活動可以包含很多款遊戲，而同一個活動下是不會有相同遊戲的。所以我們可以選擇使用(actives_id,game_name)的組合唯一性來作為本表primary key，幫助我們快速定位遊戲提供了哪些玩法。

CREATE TABLE `act_game` (
  `actives_id` bigint(20) NOT NULL COMMENT '活動ID',
  `game_name` varchar(40) NOT NULL COMMENT '遊戲名稱',
  `support_rules` text NOT NULL COMMENT '提供玩法',
  `type` tinyint(4) NOT NULL COMMENT '投資類別',
  `created_at` int(11) unsigned NOT NULL COMMENT '建立時間',
  PRIMARY KEY (`actives_id`,`game_name`)
) 

在act_name這張資料表紀錄了活動ID支援各種不同語言的翻譯，所以會有以下狀況(同id多個對應語言)，所以選擇使用(actives_id,language)的組合唯一性來作為本表primary key

actives_id | actives_name | language
------------- | -------------
1 | 投資加碼回饋 | chinese
1 | Investment plus rewards | english
1 | 投資と報酬 | japanese
... |...|...

CREATE TABLE `act_name` (
  `actives_id` bigint(20) NOT NULL COMMENT '活動ID',
  `actives_name` varchar(40) NOT NULL COMMENT '活動名稱',
  `language` varchar(40) NOT NULL COMMENT '語言',
  `created_at` int(11) unsigned NOT NULL COMMENT '建立時間',
  PRIMARY KEY (`actives_id`,`language`)
) 

刪除表資料(全部):

mysql> DELETE FROM 表名稱;

mysql> TRUNCATE TABLE 表名稱;